include("static_data.lua")

function DB.Init()
	sql.Begin()
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_budgets('team' INTEGER NOT NULL, 'budget' INTEGER NOT NULL, PRIMARY KEY('team'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_tspawns('map' TEXT NOT NULL, 'team' INTEGER NOT NULL, 'x' NUMERIC NOT NULL, 'y' NUMERIC NOT NULL, 'z' NUMERIC NOT NULL, PRIMARY KEY('map', 'team'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_privs('steam' TEXT NOT NULL, 'admin' INTEGER NOT NULL, 'mayor' INTEGER NOT NULL, 'cp' INTEGER NOT NULL, 'tool' INTEGER NOT NULL, 'phys' INTEGER NOT NULL, 'prop' INTEGER NOT NULL, PRIMARY KEY('steam'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_salaries('steam' TEXT NOT NULL, 'salary' INTEGER NOT NULL, PRIMARY KEY('steam'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_wallets('steam' TEXT NOT NULL, 'amount' INTEGER NOT NULL, PRIMARY KEY('steam'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_jails('map' TEXT NOT NULL, 'x' NUMERIC NOT NULL, 'y' NUMERIC NOT NULL, 'z' NUMERIC NOT NULL, PRIMARY KEY('map'));")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_zspawns('map' TEXT NOT NULL, 'x' NUMERIC NOT NULL, 'y' NUMERIC NOT NULL, 'z' NUMERIC NOT NULL);")
		sql.Query("CREATE TABLE IF NOT EXISTS darkrp_wiseguys('steam' TEXT NOT NULL, 'time' NUMERIC NOT NULL, PRIMARY KEY('steam'));")
	sql.Commit()

	DB.CreatePrivs()
	DB.CreateJailPos()
	DB.CreateSpawnPos()
	DB.CreateZombiePos()
end

function DB.CreatePrivs()
	if reset_all_privileges_to_these_on_startup then
		sql.Begin()
			sql.Query("DELETE FROM darkrp_privs;")
			local already_inserted = {}
			for k, v in pairs(Person) do
				local admin = 0
				local mayor = 0
				local cp = 0
				local tool = 0
				local phys = 0
				local prop = 0
				for a, b in pairs(Person[k]) do
					if b == ADMIN then admin = 1 end
					if b == MAYOR then mayor = 1 end
					if b == CP then cp = 1 end
					if b == TOOL then tool = 1 end
					if b == PHYS then phys = 1 end
					if b == PROP then prop = 1 end
				end
				if already_inserted[Person[k]] then
					sql.Query("UPDATE darkrp_privs SET admin = " .. admin .. ", mayor = " .. mayor .. ", cp = " .. cp .. ", tool = " .. tool .. ", phys = " .. phys .. ", prop = " .. prop .. " WHERE steam = " .. sql.SQLStr(Person[k]) .. ";")
				else
					sql.Query("INSERT INTO darkrp_privs VALUES(" .. sql.SQLStr(k) .. ", " .. admin .. ", " .. mayor .. ", " .. cp .. ", " .. tool .. ", " .. phys .. ", " .. prop .. ");")
					already_inserted[Person[k]] = true
				end
			end
		sql.Commit()
	end
end

function DB.CreateJailPos()
	if jail_positions then
		sql.Begin()
			sql.Query("DELETE FROM darkrp_jails;")
			for k, v in pairs(jail_positions) do
				sql.Query("INSERT INTO darkrp_jails VALUES(" .. sql.SQLStr(k) .. ", " .. v[1] .. ", " .. v[2] .. ", " .. v[3] .. ");")
			end
		sql.Commit()
	end
end

function DB.CreateSpawnPos()
	if not team_spawn_positions then return end

	sql.Begin()
		sql.Query("DELETE FROM darkrp_tspawns;")
		for k, v in pairs(team_spawn_positions) do
			sql.Query("INSERT INTO darkrp_tspawns VALUES(" .. sql.SQLStr(k) .. ", " .. v[1] .. ", " .. v[2] .. ", " .. v[3] .. ", " .. v[4] .. ");")
		end
	sql.Commit()
end

function DB.CreateZombiePos()
	if not zombie_spawn_positions then return end

	sql.Begin()
		sql.Query("DELETE FROM darkrp_zspawns;")
		for k, v in pairs(zombie_spawn_positions) do
			sql.Query("INSERT INTO darkrp_zspawns VALUES(" .. sql.SQLStr(k) .. ", " .. v[1] .. ", " .. v[2] .. ", " .. v[3] .. ");")
		end
	sql.Commit()
end

function DB.Priv2Text(priv)
	if priv == ADMIN then
		return "admin"
	elseif priv == MAYOR then
		return "mayor"
	elseif priv == CP then
		return "cp"
	elseif priv == TOOL then
		return "tool"
	elseif priv == PHYS then
		return "phys"
	elseif priv == PROP then
		return "prop"
	else
		return false
	end
end

function DB.HasPriv(ply, priv)
	local p = DB.Priv2Text(priv)
	if not p then return false end
	
	local result = tonumber(sql.QueryValue("SELECT " .. sql.SQLStr(p) .. " FROM darkrp_privs WHERE steam = " .. sql.SQLStr(ply:SteamID()) .. ";"))
	if result == 1 then return true else return false end
end

function DB.GrantPriv(ply, priv)
	local steamID = ply:SteamID()
	local p = DB.Priv2Text(priv)
	if not p then return false end
	if tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_privs WHERE steam = " .. sql.SQLStr(steamID) .. ";")) > 0 then
		sql.Query("UPDATE darkrp_privs SET " .. p .. " = 1 WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	else
		sql.Begin()
		sql.Query("INSERT INTO darkrp_privs VALUES(" .. sql.SQLStr(steamID) .. ", 0, 0, 0, 0, 0, 0);")
		sql.Query("UPDATE darkrp_privs SET " .. p .. " = 1 WHERE steam = " .. sql.SQLStr(steamID) .. ";")
		sql.Commit()
	end
	return true
end

function DB.RevokePriv(ply, priv)
	local steamID = ply:SteamID()
	local p = DB.Priv2Text(priv)
	local val = tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_privs WHERE steam = " .. sql.SQLStr(steamID) .. ";"))
	if not p or val < 1 then return false end
	sql.Query("UPDATE darkrp_privs SET " .. p .. " = 0 WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	return true
end

function DB.StoreJailPos(ply)
	local pos = string.Explode(" ", tostring(ply:GetPos()))
	local already = tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_jails WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";"))
	if not already or already == 0 then
		sql.Query("INSERT INTO darkrp_jails VALUES(" .. sql.SQLStr(game.GetMap()) .. ", " .. pos[1] .. ", " .. pos[2] .. ", " .. pos[3] .. ");")
		Notify(ply, 1, 4,  "Jail position created.")
	else
		sql.Query("UPDATE darkrp_jails SET x = " .. pos[1] .. ", y = " .. pos[2] .. ", z = " .. pos[3] .. " WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";")
		Notify(ply, 1, 4,  "Jail position updated.")
	end
end

function DB.RetrieveJailPos()
	local r = sql.Query("SELECT x, y, z FROM darkrp_jails WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";")

	if not r then return false end

	for map, row in pairs(r) do
		return Vector(row['x'], row['y'], row['z'])
	end
end

function DB.StoreJailStatus(ply, time)
	local steamID = ply:SteamID()
	-- Is there an existing outstanding jail sentence for this player?
	local r = sql.QueryValue("SELECT time FROM darkrp_wiseguys WHERE steam = " .. sql.SQLStr(steamID) .. ";")

	
	if not r and time ~= 0 then
		-- If there is no jail record for this player and we're not trying to clear an existing one
		sql.Query("INSERT INTO darkrp_wiseguys VALUES(" .. sql.SQLStr(steamID) .. ", " .. time .. ");")
	else
		-- There is a jail record for this player
		if time == 0 then
			-- If we are reducing their jail time to zero, delete their record
			sql.Query("DELETE FROM darkrp_wiseguys WHERE steam = " .. sql.SQLStr(steamID) .. ";")
		else
			-- Increase this player's sentence by the amount specified
			sql.Query("UPDATE darkrp_wiseguys SET time = " .. r + time .. " WHERE steam = " .. sql.SQLStr(steamID) .. ");")
		end
	end
end

function DB.RetrieveJailStatus(ply)
	-- How much time does this player owe in jail?
	local r = sql.QueryValue("SELECT time FROM darkrp_wiseguys WHERE steam = " .. sql.SQLStr(ply:SteamID()) .. ";")
	if r then
		return r
	else
		return 0
	end
end

function DB.StoreTeamSpawnPos(t, pos)
	local already = tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_tspawns WHERE team = " .. t .. " AND map = " .. sql.SQLStr(game.GetMap()) .. ";"))
	if not already or already == 0 then
		sql.Query("INSERT INTO darkrp_tspawns VALUES(" .. sql.SQLStr(game.GetMap()) .. ", " .. t .. ", " .. pos[1] .. ", " .. pos[2] .. ", " .. pos[3] .. ");")
		Notify(ply, 1, 4,  "Spawn position created.")
	else
		sql.Query("UPDATE darkrp_tspawns SET x = " .. pos[1] .. ", y = " .. pos[2] .. ", z = " .. pos[3] .. " WHERE team = " .. t .. " AND map = " .. sql.SQLStr(game.GetMap()) .. ";")
		Notify(ply, 1, 4,  "Spawn position updated.")
	end
end

function DB.RetrieveTeamSpawnPos(ply)
	local r = sql.Query("SELECT * FROM darkrp_tspawns WHERE map = " .. sql.SQLStr(game.GetMap()) .. " AND team = " .. ply:Team() .. ";")

	if not r then return nil end

	for map, row in pairs(r) do
		return Vector(row['x'], row['y'], row['z'])
	end
end

function DB.StoreZombies()
	sql.Begin()
	sql.Query("DELETE FROM darkrp_zspawns WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";")

	for k, v in pairs(zombieSpawns) do
		local s = string.Explode(" ", v)
		sql.Query("INSERT INTO darkrp_zspawns VALUES(" .. sql.SQLStr(game.GetMap()) .. ", " .. s[1] .. ", " .. s[2] .. ", " .. s[3] .. ");")
	end
	sql.Commit()
end

function DB.RetrieveZombies()
	zombieSpawns = {}
	local r = sql.Query("SELECT * FROM darkrp_zspawns WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";")
	if not r then return end
	for map, row in pairs(r) do
		zombieSpawns[map] = tostring(row['x']) .. " " .. tostring(row['y']) .. " " .. tostring(row['x'])
	end
end

function DB.RetrieveRandomZombieSpawnPos()
	local r = false
	local c = tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_zspawns WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";"))
	if c and c >= 1 then
		r = sql.QueryRow("SELECT * FROM darkrp_zspawns WHERE map = " .. sql.SQLStr(game.GetMap()) .. ";", math.random(1, c))
		return Vector(r['x'], r['y'], r['z'])
	else
		return Vector(0,0,0)
	end
end

function DB.StoreMoney(ply, amount)
	local steamID = ply:SteamID()
	local r = sql.QueryValue("SELECT amount FROM darkrp_wallets WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	if r then
		sql.Query("UPDATE darkrp_wallets SET amount = " .. amount .. " WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	else
		sql.Query("INSERT INTO darkrp_wallets VALUES(" .. sql.SQLStr(steamID) .. ", " .. amount .. ");")
	end
	ply:SetNWInt("money", math.floor(amount))
end

function DB.RetrieveMoney(ply)
	local steamID = ply:SteamID()
	local startingAmount = 500
		
	local r = sql.QueryValue("SELECT amount FROM darkrp_wallets WHERE steam = " .. sql.SQLStr(ply:SteamID()) .. ";")
	if r then
		ply:SetNWInt("money", math.floor(r))
		return r
	else
		-- No record yet, setting starting cash to 500
		DB.StoreMoney(ply, startingAmount)
		return startingAmount
	end
end

function DB.PayPlayer(ply1, ply2, amount)
	local sid1 = ply1:SteamID()
	local sid2 = ply2:SteamID()
	sql.Begin() -- Transaction
		sql.Query("UPDATE darkrp_wallets SET amount = amount - " ..  amount .. " WHERE steam = " .. sql.SQLStr(sid1) .. ";")
		sql.Query("UPDATE darkrp_wallets SET amount = amount + " ..  amount .. " WHERE steam = ".. sql.SQLStr(sid2) .. ";")
	sql.Commit()
	DB.RetrieveMoney(ply1)
	DB.RetrieveMoney(ply2)
end

function DB.StoreSalary(ply, amount)
	local steamID = ply:SteamID()
	local already = tonumber(sql.QueryValue("SELECT COUNT(*) FROM darkrp_salaries WHERE steam = " .. sql.SQLStr(steamID) .. ";"))
	if not already or already == 0 then
		sql.Query("INSERT INTO darkrp_salaries VALUES(" .. sql.SQLStr(steamID) .. ", " .. amount .. ");")
	else
		sql.Query("UPDATE darkrp_salaries SET salary = " .. amount .. " WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	end
	ply:SetNWInt("salary", math.floor(amount))
	return amount
end

function DB.RetrieveSalary(ply)
	local steamID = ply:SteamID()
	local normal = CfgVars["normalsalary"]

	local r = sql.QueryValue("SELECT amount FROM darkrp_salaries WHERE steam = " .. sql.SQLStr(steamID) .. ";")
	if not r then
		DB.StoreSalary(ply, normal)
		return normal
	else
		return r
	end
end